﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml.Schema;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class SchemaObject 
    {
        public SchemaObject()
        {

        }

        public static Guid CreateSchemaObject(DTO.SchemaObject schemaObject, Guid parentID, Guid schemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaIDParam = new SqlParameter("@schemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaObjectTypeParam = new SqlParameter("@schemaObjectType", SqlDbType.SmallInt);
                    SqlParameter SchemaObjectNameParam = new SqlParameter("@schemaObjectName", SqlDbType.NVarChar, 256);
                    SqlParameter SchemaObjectXmlParam = new SqlParameter("@schemaObjectXml", SqlDbType.Xml);
                    SqlParameter ParentIDParam = new SqlParameter("@parentID", SqlDbType.UniqueIdentifier);
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Direction = ParameterDirection.Output;
                    SchemaIDParam.Value = schemaID;
                    SchemaObjectTypeParam.Value = Convert.ToInt16(schemaObject.Type);
                    SchemaObjectNameParam.Value = schemaObject.Name;
                    SchemaObjectXmlParam.Value = schemaObject.Xml.OuterXml;
                    ParentIDParam.Value = parentID;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(SchemaObjectTypeParam);
                    cmd.Parameters.Add(SchemaObjectXmlParam);
                    cmd.Parameters.Add(SchemaObjectNameParam);
                    if (parentID == Guid.Empty)
                        ParentIDParam.Value = null;
                    else
                        ParentIDParam.Value = parentID;

                    cmd.ExecuteNonQuery();
                    schemaObject.ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return schemaObject.ID;
        }


        public static Guid UpdateSchemaObject(DTO.SchemaObject schemaObject, Guid existingSchemaObjectID, Guid parentID, Guid schemaID,
                                                SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateSchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaObjectIDParam = new SqlParameter("@schemaObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaIDParam = new SqlParameter("@schemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaObjectTypeParam = new SqlParameter("@schemaObjectType", SqlDbType.SmallInt);
                    SqlParameter SchemaObjectNameParam = new SqlParameter("@schemaObjectName", SqlDbType.NVarChar, 256);
                    SqlParameter SchemaObjectXmlParam = new SqlParameter("@schemaObjectXml", SqlDbType.Xml);
                    SqlParameter ParentIDParam = new SqlParameter("@parentID", SqlDbType.UniqueIdentifier);
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Direction = ParameterDirection.Output;
                    SchemaObjectIDParam.Value = existingSchemaObjectID;
                    SchemaIDParam.Value = schemaID;
                    SchemaObjectTypeParam.Value = Convert.ToInt16(schemaObject.Type);
                    SchemaObjectNameParam.Value = schemaObject.Name;
                    SchemaObjectXmlParam.Value = schemaObject.Xml.OuterXml;
                    ParentIDParam.Value = parentID;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(SchemaObjectIDParam);
                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(SchemaObjectTypeParam);
                    cmd.Parameters.Add(SchemaObjectXmlParam);
                    cmd.Parameters.Add(SchemaObjectNameParam);
                    if (parentID == Guid.Empty)
                        ParentIDParam.Value = null;
                    else
                        ParentIDParam.Value = parentID;

                    cmd.ExecuteNonQuery();
                    schemaObject.ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return schemaObject.ID;
        }

        public static DTO.SchemaObject GetSchemaObject(Guid schemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SchemaObject schemaObject = new DTO.SchemaObject();
            const string sprocname = "[ServiceEngine].[GetSchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schemaObjectID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            schemaObject.ID = reader.GetGuid(0);
                            schemaObject.Name = reader.GetString(1);
                            schemaObject.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(3).CreateReader());
                            schemaObject.Xml = xdoc.DocumentElement;
                            schemaObject.NamespaceUri = reader.GetString(4);
                            if (reader.IsDBNull(5))
                            {
                                schemaObject.DataEntityID = Guid.Empty;
                            }
                            else
                            {
                                schemaObject.DataEntityID = reader.GetGuid(5);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return schemaObject;
        }

        public static List<DTO.SchemaObject> ListSchemaObjectByNameTypeAndNamespace(string namespaceUri, string name, DTO.SchemaObjectType type,
                                                                            SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.SchemaObject> schemaObjects = null;
            const string sprocname = "[ServiceEngine].[ListSchemaObjectByNameTypeAndNamespace]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                Guid schemaID = Entity.GetIDFromName(namespaceUri, DTO.EntityClass.Schema, txn, conn);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaIDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaObjectTypeParam = new SqlParameter("@Type", SqlDbType.SmallInt);
                    SqlParameter SchemaObjectNameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 256);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SchemaIDParam.Value = schemaID;
                    SchemaObjectTypeParam.Value = Convert.ToInt16(type);
                    SchemaObjectNameParam.Value = name;

                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(SchemaObjectTypeParam);
                    cmd.Parameters.Add(SchemaObjectNameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            schemaObjects = new List<DTO.SchemaObject>();
                            while (reader.Read())
                            {
                                DTO.SchemaObject schemaObject = new DTO.SchemaObject();
                                schemaObject.ID = reader.GetGuid(0);
                                schemaObject.Name = reader.GetString(1);
                                schemaObject.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(3).CreateReader());
                                schemaObject.Xml = xdoc.DocumentElement;
                                schemaObject.NamespaceUri = reader.GetString(4);
                                if (reader.IsDBNull(5))
                                {
                                    schemaObject.DataEntityID = Guid.Empty;
                                }
                                else
                                {
                                    schemaObject.DataEntityID = reader.GetGuid(5);
                                }
                                schemaObject.Revision = new DTO.SchemaObjectRevision();
                                if (reader.IsDBNull(6))
                                {
                                    schemaObject.Revision.IsLatest = true;
                                }
                                else
                                {
                                    schemaObject.Revision.IsLatest = false;
                                    schemaObject.Revision.DateRevised = reader.GetDateTime(6);
                                } 
                                schemaObjects.Add(schemaObject);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObjects;
        }

        public static DTO.SchemaObject GetSchemaObjectByNameTypeAndNamespace(string namespaceUri, string name, DTO.SchemaObjectType type,
                                                                            SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SchemaObject schemaObject = null;
            const string sprocname = "[ServiceEngine].[GetSchemaObjectByNameTypeAndNamespace]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                Guid schemaID = Entity.GetIDFromName(namespaceUri, DTO.EntityClass.Schema, txn, conn);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaIDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaObjectTypeParam = new SqlParameter("@Type", SqlDbType.SmallInt);
                    SqlParameter SchemaObjectNameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 256);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SchemaIDParam.Value = schemaID;
                    SchemaObjectTypeParam.Value = Convert.ToInt16(type);
                    SchemaObjectNameParam.Value = name;

                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(SchemaObjectTypeParam);
                    cmd.Parameters.Add(SchemaObjectNameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            schemaObject = new DTO.SchemaObject();
                            reader.Read();
                            schemaObject.ID = reader.GetGuid(0);
                            schemaObject.Name = reader.GetString(1);
                            schemaObject.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(3).CreateReader());
                            schemaObject.Xml = xdoc.DocumentElement;
                            schemaObject.NamespaceUri = reader.GetString(4);
                            if (reader.IsDBNull(5))
                            {
                                schemaObject.DataEntityID = Guid.Empty;
                            }
                            else
                            {
                                schemaObject.DataEntityID = reader.GetGuid(5);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObject;
        }

        public static DTO.SchemaObject GetSchemaObjectByID(Guid schemaObjectId, bool loadDependencies, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.SchemaObject schemaObject = null;
            const string sprocname = "[ServiceEngine].[GetSchemaObjectByID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaObjectIDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SchemaObjectIDParam.Value = schemaObjectId;

                    cmd.Parameters.Add(SchemaObjectIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            schemaObject = new DTO.SchemaObject();
                            reader.Read();
                            schemaObject.ID = reader.GetGuid(0);
                            schemaObject.Name = reader.GetString(1);
                            schemaObject.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(3).CreateReader());
                            schemaObject.Xml = xdoc.DocumentElement;
                            schemaObject.NamespaceUri = reader.GetString(4);
                            if (reader.IsDBNull(5))
                            {
                                schemaObject.DataEntityID = Guid.Empty;
                            }
                            else
                            {
                                schemaObject.DataEntityID = reader.GetGuid(5);
                            }
                        }
                    }
                    if (schemaObject != null && loadDependencies == true)
                    {
                        List<DTO.SchemaObject> schemaObjects = ListSchemaObjectDependencies(schemaObjectId, txn, conn);
                        if (schemaObjects != null && schemaObjects.Count > 0)
                        {
                            DTO.SchemaObjectList dependencies = new DTO.SchemaObjectList();
                            dependencies.SchemaObjects = new List<DTO.SchemaObjectListElement>();
                            schemaObjects.ForEach(x => dependencies.SchemaObjects.Add(new DTO.SchemaObjectListElement
                                                  {
                                                      ID = x.ID,
                                                      Name = x.Name,
                                                      Type = x.Type,
                                                      NamespaceUri = x.NamespaceUri                                                      
                                                  }));
                            schemaObject.Dependencies = dependencies;
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObject;
        }

        //TODO Accessed from schema.cs. Future refactoring candidate         
        public static bool AddTypeToSchemaAndHash(DTO.SchemaObject schemaObject, Dictionary<string, XmlSchema> schemaHash, 
                                                    Dictionary<Guid, string> mappedTypeHash,SqlTransaction txn, SqlConnection conn)
        {
            bool existAlready = false;
            // Check to make sure if the schema object's namespace is not the default one 
            if (string.Compare(schemaObject.NamespaceUri, XmlSchema.Namespace, true) != 0)
            {
                // We need to check if the schemaObject of the same name, type and namespace exists
                string key = CatalogSvcHelper.CreateKey(schemaObject.Name, schemaObject.NamespaceUri, schemaObject.Type);
                //if (mappedTypeHash.ContainsKey(schemaObject.ID) == false)
                if (mappedTypeHash.ContainsValue(key) == false)
                {
                    XmlSchema xs = CatalogSvcHelper.GetSchemaFromMap(schemaHash, schemaObject.NamespaceUri,txn,conn);
                    XmlSchemaObject xsObject = CatalogSvcHelper.DeSerializeSchemaObject(schemaObject.Xml.OuterXml, new XmlQualifiedName(schemaObject.Name, schemaObject.NamespaceUri));
                    xs.Items.Add(xsObject);
                    mappedTypeHash.Add(schemaObject.ID, key);
                }
                else
                {
                    existAlready = true;
                }
            }
            return existAlready;
        }

        //TODO Accessed from schema.cs. Future refactoring candidate 
        public static void AppendDependencyObjectsToSchema(DTO.SchemaObject schemaObject,
                                            Dictionary<string, XmlSchema> schemaHash, Dictionary<Guid, string> mappedTypeHash,
                                            SqlTransaction txn, SqlConnection conn)
        {
            List<DTO.SchemaObject> dependencies = ListSchemaObjectDependencies(schemaObject.ID, null, null);
            if (dependencies.Count > 0)
            {
                foreach (DTO.SchemaObject key in dependencies)
                {
                    //Console.WriteLine("SchemaObject Added Name=[{0}] NS=[{1}]", key.Name, key.NamespaceUri);
                    if (AddTypeToSchemaAndHash(key, schemaHash, mappedTypeHash,txn,conn) == false)
                    {
                        AppendDependencyObjectsToSchema(key, schemaHash, mappedTypeHash,txn,conn);
                    }
                }
            }
        }

        public static Guid CreateSchemaObjectDependency(Guid SchemaObjectID, Guid parentID, string importNamespace, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSchemaObjectDependency]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaObjectIDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter ParentIDParam = new SqlParameter("@ParentObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter ImportNSParam = new SqlParameter("@ImportNamespace", SqlDbType.NVarChar, 256);
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Direction = ParameterDirection.Output;
                    SchemaObjectIDParam.Value = SchemaObjectID;
                    ParentIDParam.Value = parentID;
                    ImportNSParam.Value = importNamespace;

                    cmd.Parameters.Add(SchemaObjectIDParam);
                    cmd.Parameters.Add(ParentIDParam);
                    cmd.Parameters.Add(ImportNSParam);
                    cmd.Parameters.Add(IDParam);
                    cmd.ExecuteNonQuery();
                    return (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }


        public static void DeleteSchemaObject(Guid SchemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter SchemaObjectIDParam = new SqlParameter("@schemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    SchemaObjectIDParam.Value = SchemaObjectID;

                    cmd.Parameters.Add(SchemaObjectIDParam);
                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }


        public static void UpdateSchemaObjectReferences(Guid oldSchemaObjectID, Guid newSchemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateSchemaObjectReferences]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OldSchemaObjectIDParam = new SqlParameter("@OldSchemaObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter NewSchemaObjectIDParam = new SqlParameter("@newSchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    OldSchemaObjectIDParam.Value = oldSchemaObjectID;
                    NewSchemaObjectIDParam.Value = newSchemaObjectID;

                    cmd.Parameters.Add(OldSchemaObjectIDParam);
                    cmd.Parameters.Add(NewSchemaObjectIDParam);
                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }


        public static List<DTO.SchemaObject> ListSchemaObjectDependencies(Guid schemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.SchemaObject> schemaObjects = new List<DTO.SchemaObject>();
            const string sprocname = "[ServiceEngine].[ListSchemaObjectDependencies]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ParentObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schemaObjectID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.SchemaObject schemaObject = new DTO.SchemaObject();
                                schemaObject.ID = reader.GetGuid(0);
                                schemaObject.Name = reader.GetString(1);
                                schemaObject.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(3).CreateReader());
                                schemaObject.Xml = xdoc.DocumentElement;
                                schemaObject.NamespaceUri = reader.GetString(4);
                                if (reader.IsDBNull(5))
                                {
                                    schemaObject.DataEntityID = Guid.Empty;
                                }
                                else
                                {
                                    schemaObject.DataEntityID = reader.GetGuid(5);
                                } 
                                schemaObjects.Add(schemaObject);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return schemaObjects;
        }

        public static List<DTO.Entity> ListSchemaObjectReferences(Guid schemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.Entity> referencedByEntities = new List<DTO.Entity>();
            const string sprocname = "[ServiceEngine].[ListSchemaObjectReferences]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schemaObjectID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.Entity schemaObject = new DTO.Entity();
                                schemaObject.Name = reader.GetString(0);
                                schemaObject.ID = reader.GetGuid(1);
                                schemaObject.EntityClass = DTO.EntityClass.SchemaObject;
                                referencedByEntities.Add(schemaObject);
                            }
                        }
                    }
                }
                // Add all the dependent Resources
                List<DTO.Entity> entities = Data.Resource.ListResourcesForSchemaObject(schemaObjectID, txn, conn);
                if (entities != null && entities.Count() > 0)
                    entities.ForEach(item => referencedByEntities.Add(item));

                // Add all the dependent Operations
                entities = Data.OperationVersion.ListOperationVersionsBySchemaObject(schemaObjectID, txn, conn);
                if (entities != null && entities.Count() > 0)
                    entities.ForEach(item => referencedByEntities.Add(item));
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return referencedByEntities;
        }

        public static List<DTO.SchemaObject> ListSchemaObjectForSchema(Guid schemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[ListSchemaObjectsBySchemaID]";

            List<DTO.SchemaObject> schemaObjects = new List<DTO.SchemaObject>();
            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schemaID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.SchemaObject so = new DTO.SchemaObject();
                                so.ID = reader.GetGuid(0);
                                so.Name = reader.GetString(1);
                                so.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(3).CreateReader());
                                so.Xml = xdoc.DocumentElement;
                                so.Revision = new DTO.SchemaObjectRevision();
                                so.Revision.IsLatest = true;
                                if (reader.IsDBNull(4))
                                {
                                    so.DataEntityID = Guid.Empty;
                                }
                                else
                                {
                                    so.DataEntityID = reader.GetGuid(4);
                                }
                                so.NamespaceUri = reader.GetString(5);
                                schemaObjects.Add(so);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObjects;
        }
        public static List<DTO.SchemaObject> ListSchemaObjectForSchemaNamespace(string schemaNamespace, SqlTransaction txn, SqlConnection conn)
        {
            Guid id = Data.Entity.GetIDFromName(schemaNamespace, DTO.EntityClass.Schema, txn, conn);
            return ListSchemaObjectForSchema(id, txn, conn);
        }
        public static DTO.SchemaObjectList ListSchemaObjects(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[ListSchemaObjects]";

            DTO.SchemaObjectList schemaObjectList = new DTO.SchemaObjectList();
            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            schemaObjectList.SchemaObjects = new List<DTO.SchemaObjectListElement>();
                            while (reader.Read())
                            {
                                DTO.SchemaObjectListElement sole = new DTO.SchemaObjectListElement();
                                sole.ID = reader.GetGuid(0);
                                sole.Name = reader.GetString(1);
                                sole.Type = (DTO.SchemaObjectType)reader.GetInt16(2);
                                schemaObjectList.SchemaObjects.Add(sole);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObjectList;
        }
        public static List<DTO.SchemaObject> ListSchemaObjectForDataEntityID(Guid dataEntityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[ListSchemaObjectsByDataEntityID]";

            List<DTO.SchemaObject> schemaObjects = new List<DTO.SchemaObject>();
            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@DataEntityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = dataEntityID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.SchemaObject so = new DTO.SchemaObject();
                                so.NamespaceUri = reader.GetString(0);
                                so.ID = reader.GetGuid(1);
                                so.Name = reader.GetString(2);
                                so.Type = (DTO.SchemaObjectType)reader.GetInt16(3);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(4).CreateReader());
                                so.Xml = xdoc.DocumentElement;
                                so.Revision = new DTO.SchemaObjectRevision();
                                if (reader.IsDBNull(5))
                                {
                                    so.Revision.IsLatest = true;
                                }
                                else
                                {
                                    so.Revision.IsLatest = false;
                                    so.Revision.DateRevised = reader.GetDateTime(5);
                                }
                                if (reader.IsDBNull(6))
                                {
                                    so.DataEntityID = Guid.Empty;
                                }
                                else
                                {
                                    so.DataEntityID = reader.GetGuid(6);
                                }
                                schemaObjects.Add(so);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return schemaObjects;
        }
    }
}
